package org.hepeng.workx.mybatis.interceptor;

import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.util.deparser.ExpressionDeParser;
import net.sf.jsqlparser.util.deparser.SelectDeParser;
import org.hepeng.workx.sqlparse.SelectParser;

import java.util.Objects;

/**
 * @author he peng
 */
public class OraclePageQuerySQLRewriter extends AbstractPageQuerySQLRewriter {

    private static final String ORACLE_ROW_NUM_COLUMN_NAME = "ROWNUM";

    @Override
    protected String doRewrite(String sql, Long startRow, Integer pageSize, Long endRow) throws Exception {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT * FROM ( ");

        Select select = (Select) CCJSqlParserUtil.parse(sql);
        ExpressionDeParser expressionDeParser = new ExpressionDeParser();
        SelectDeParser parser = new SelectParser(expressionDeParser , builder);
        expressionDeParser.setSelectVisitor(parser);
        expressionDeParser.setBuffer(builder);

        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        Column selectRowNumColumn = new Column();
        selectRowNumColumn.setColumnName(ORACLE_ROW_NUM_COLUMN_NAME);
        Alias alias = new Alias("rowno");
        SelectExpressionItem selectItem = new SelectExpressionItem(selectRowNumColumn);
        selectItem.setAlias(alias);
        plainSelect.getSelectItems().add(selectItem);

        Column column = new Column();
        column.setColumnName(ORACLE_ROW_NUM_COLUMN_NAME);
        LongValue longValue = new LongValue(endRow);
        MinorThanEquals minorThanEquals = new MinorThanEquals();
        minorThanEquals.setLeftExpression(column);
        minorThanEquals.setRightExpression(longValue);

        Expression originalWhere = plainSelect.getWhere();
        Expression newWhere = minorThanEquals;
        if (Objects.nonNull(originalWhere)) {
            newWhere = new AndExpression(originalWhere , minorThanEquals);
        }

        plainSelect.setWhere(newWhere);
        plainSelect.accept(parser);

        builder.append(" ) data_table ")
                .append(" WHERE data_table." + alias.getName() + " >= ")
                .append(startRow);

        return builder.toString();
    }
}
